[mysql]
auto-rehash

[mysqld]
####: for global
user                                ={{ mysql_user }}                        #   mysql
basedir                             ={{ mysql_base_dir }}               #   /usr/local/mysql/
datadir                             ={{ mysql_data_dir_base }}/data/{{ mysql_port }}    #   /usr/local/mysql/data
tmpdir                              ={{ mysql_data_dir_base }}/tmp/{{ mysql_port }}
server_id                           ={{ mysql_server_id }}                           #   0
port                                ={{ mysql_port }}                         #   3306
character_set_server                ={{ mysql_character_set_server }}  #   utf8mb4
log_bin_trust_function_creators     =on                             #   0
max_prepared_stmt_count             =1048576                        #   16382
log_timestamps                      =system                         #   utc
pid_file                            =mysqld.pid
read_only                           =0                              #   off
super_read_only                     =0                              #   off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #   1
auto_increment_offset               =1                              #   1
lower_case_table_names              =1                              #   0
open_files_limit                    =65536                          #   5000
max_connections                     ={{ mysql_max_connections | default(1000) }}                            #   151
thread_cache_size                   ={{ [(server_specs_processor_count | int ) * 8, 256 ] | max }}                              #   9
table_open_cache                    =4096                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
local_infile                        =off                            #   off
disabled_storage_engines=archive,blackhole,example,federated,memory,merge,ndb,myisam
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;validate_password=validate_password.so"
transaction_isolation               ={{ mysql_transaction_isolation }} #RR
{% if sock_type == 1 %}
socket                                  = mysql.sock                 #    /tmp/mysql.sock          
mysqlx_socket                           = mysqlx.sock                #    /tmp/mysqlx.sock
{% elif sock_type == 2 %}
socket                                  = /tmp/mysql{{ mysql_port }}.sock
mysqlx_socket                           = /tmp/mysqlx{{ mysql_port }}.sock
{% elif sock_type == 3 %}
socket                                  = {{ mysql_data_dir_base }}/run/mysql{{ mysql_port }}.sock
mysqlx_socket                           = {{ mysql_data_dir_base }}/run/mysqlx{{ mysql_port }}.sock
{% endif %}


####: for binlog
binlog_format                       ={{ mysql_binlog_format }}      #   row
binlog_error_action                 =abort_server          # abort_server
log_bin                             ={{ mysql_data_dir_base }}/log/binlog/{{ mysql_port }}/mysql-bin                      #   off
binlog_rows_query_log_events        =on                             #   off
log_slave_updates                   =on                             #   off
{# set binlog cache size #}
{% if mysql_binlog_format == 'mixed' %}
binlog_cache_size                   =32768                          #   32768(32k)
{% else %}
binlog_cache_size                   =65536                          #   65536(64k)
{% endif %}
{# set binlog cache size #}
binlog_checksum                     =CRC32                          #   CRC32
sync_binlog                         =1                              #   1
slave_preserve_commit_order         =ON                             #  OFF 


####: for error-log
log_error                           =mysql-error.log                #   /usr/local/mysql/data/localhost.localdomain.err

general_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
log_queries_not_using_indexes       =off                            #    off
long_query_time                     =2.000000                       #    10.000000

####: for gtid
gtid_executed_compression_period    =1000                           #    1000
gtid_mode                           =on                             #    off
enforce_gtid_consistency            =on                             #    off


####: for replication
skip_slave_start                    =0                              #   0
relay_log                           ={{ mysql_data_dir_base }}/log/relaylog/{{ mysql_port }}/relay-bin
relay_log_recovery=1
master_info_repository              =table                         #   file
relay_log_info_repository           =table                         #   file
slave_parallel_type                 =logical_clock                 #   database | LOGICAL_CLOCK
slave_parallel_workers              ={{ [(server_specs_processor_count | int ) * 4 ,32] | min }}                             #    0
loose-rpl_semi_sync_master_enabled        =1                             #    0
loose-rpl_semi_sync_slave_enabled         =1                             #    0
loose-rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_group_commit_sync_delay      =500                           #    0      500(0.05% seconde) 
binlog_group_commit_sync_no_delay_count = 13                       #    0
{% if use_write_set == 1 %}
binlog_transaction_dependency_tracking  = WRITESET                 #    COMMIT_ORDER          
transaction_write_set_extraction        = XXHASH64
{% endif %}


####: for innodb
default_storage_engine                          =innodb                     #   innodb
default_tmp_storage_engine                      =innodb                     #   innodb
innodb_data_file_path                           =ibdata1:64M:autoextend     #   ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_pool
innodb_log_group_home_dir                       ={{ mysql_data_dir_base }}/log/redolog/{{ mysql_port }}                         #   ./
{% if server_specs != "auto" %}
{% set server_specs_memtotal_gb_int = server_specs_memtotal_gb | int %}
{% if mysql_version is version('8.0.30', '<') %}
{% if (server_specs_memtotal_gb_int * 0.6) < 8 %}
innodb_log_file_size = 512MB
innodb_log_files_in_group = {{ max((server_specs_memtotal_gb_int * 0.6) | round(0, 'ceil') | int, 2) }}
{% elif 8 <= (server_specs_memtotal_gb_int * 0.6) <= 128 %}
innodb_log_file_size = 1024MB
innodb_log_files_in_group = {{ max((server_specs_memtotal_gb_int * 0.6 * 0.75) | round(0, 'ceil') | int, 2) }}
{% else %}
innodb_log_file_size = 2048MB
innodb_log_files_in_group = 64
{% endif %}
{% else %}
{% if server_specs_memtotal_gb_int < 2 %}
innodb_redo_log_capacity = 100MB
{% elif 2 <= server_specs_memtotal_gb_int < 4 %}
innodb_redo_log_capacity = 1GB
{% elif 4 <= server_specs_memtotal_gb_int < 11 %}
innodb_redo_log_capacity = {{ (0.75 * server_specs_memtotal_gb_int * 0.5) | round(0, 'ceil') | int }}GB
{% elif 11 <= server_specs_memtotal_gb_int < 171 %}
innodb_redo_log_capacity = {{ (0.5625 * server_specs_memtotal_gb_int * 0.5) | round(0, 'ceil') | int }}GB
{% else %}
innodb_redo_log_capacity = 128GB
{% endif %}
{% endif %}
{% endif %}
innodb_file_per_table                           =on                         #   on
innodb_online_alter_log_max_size                =128M                       #   134217728(128M)
innodb_open_files                               ={{ mysql_innodb_open_files }}                       #   -1 (auto)
innodb_page_size                                =16k                        #   16384(16k)
innodb_thread_concurrency                       =0                          #   0
innodb_read_io_threads                          =4                          #   4
innodb_write_io_threads                         =4                          #   4
innodb_purge_threads                            =4                          #   4(garbage collection)
innodb_page_cleaners                            =4                          #   4(flush lru list)
innodb_print_all_deadlocks                      =on                         #   off
innodb_deadlock_detect                          =on                         #   on
innodb_lock_wait_timeout                        =50                         #   50
innodb_spin_wait_delay                          =6                          #   6
innodb_autoinc_lock_mode                        =2                          #   1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #   on
innodb_stats_persistent_sample_pages            =20                         #   20
{# --                 set innodb_buffer_pool_instances                                 -- #}
{% if ((server_specs_memtotal_gb | int ) * 0.6 ) < 64  %}
innodb_buffer_pool_instances                    ={{ [ (((server_specs_memtotal_gb | int ) * 0.6) | round(0, 'ceil') | int ) , 1 ] | max }}
{% else %}
innodb_buffer_pool_instances                    =64
{% endif %}
{# --                 set innodb_buffer_pool_instances                                 -- #}
innodb_adaptive_hash_index                      =on                         #   on
innodb_change_buffering                         =all                        #   all
innodb_change_buffer_max_size                   =25                         #   25
innodb_flush_neighbors                          =0                          #   0
#innodb_flush_method                            =O_DIRECT                   #   fsync
innodb_doublewrite                              =on                         #   on
innodb_log_buffer_size                          ={{ mysql_innodb_log_buffer_size }}                        #   16777216(16M)
innodb_flush_log_at_timeout                     =1                          #   1
innodb_flush_log_at_trx_commit                  =1                          #   1
{% if server_specs != "auto" %}
innodb_buffer_pool_size                         ={{ ((server_specs_memtotal_gb | int ) * 1024 * 0.6 // 128 * 128 ) | round(0, 'ceil') | int }}M  # 134217728(128M)
{% endif %}
autocommit                                      =1                          #   1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25 
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON

#--------undo log
innodb_undo_log_truncate                        = 1                         #    0
innodb_max_undo_log_size                        = 2G                        #    1G
#innodb_undo_logs                               = 128                       #    128   
#innodb_undo_tablespaces                        = 2                         #    2

report_host = {{ ansible_default_ipv4.address }}
default_time_zone = {{ mysql_default_time_zone }}

{% if mtls_with_mysql_group_replication == 1 %}
####: for mysql group replication 
loose-group_replication_recovery_retry_count          =10                                         #   10
loose-group_replication_recovery_reconnect_interval   =60                                         #   60
loose-group_replication_allow_local_disjoint_gtids_join=off                                       #   off
loose-group_replication_allow_local_lower_version_join=off                                        #   off
loose-group_replication_ip_whitelist                  =AUTOMATIC                                  #   AUTOMATIC
loose-transaction_write_set_extraction                =XXHASH64                                   #   off
loose-group_replication_group_name                    ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"     #
loose-group_replication_start_on_boot                 =off                                        #   off
loose-group_replication_bootstrap_group               =off                                        #   off
loose-group_replication_single_primary_mode           =on                                         #   on
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_gtid_assignment_block_size    =1000000                                    #   1000000
loose-group_replication_poll_spin_loops               =0                                          #   0
loose-group_replication_compression_threshold         =1024                                       #   1000000
loose-group_replication_flow_control_mode             =QUOTA                                      #   QUOTA
loose-group_replication_consistency                   =BEFORE_ON_PRIMARY_FAILOVER                 #   EVENTUAL
loose-group_replication_exit_state_action             =OFFLINE_MODE                               #   READ_ONLY
loose-group_replication_paxos_single_leader           =on                                         #   off
{% for ip in ansible_all_ipv4_addresses %}
    {%- for host_ip in mysql_mgr_hosts %}
        {%- if ip == host_ip %}
loose-group_replication_local_address                 ="{{ ip }}:{{ mysql_mgr_port }}"
report_host                                           ="{{ ip }}"
        {%- endif %}
    {%- endfor %}
{% endfor %}

{% set gs = ':' + (mysql_mgr_port | string)+',' %}
loose-group_replication_group_seeds                   ="{{ mysql_mgr_hosts | join(gs) + ':' + (mysql_mgr_port | string) }}"
{% endif %}


####  for performance_schema
performance_schema                                                      =on    #    on

																					  
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_events_statements_cpu                       =off   #    off
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_transactions_current                 =on    #    on
performance_schema_consumer_events_transactions_history                 =on    #    on
performance_schema_consumer_events_transactions_history_long            =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance_schema_consumer_global_instrumentation                      =on    # on
performance_schema_consumer_thread_instrumentation                      =on    # on
performance_schema_consumer_statements_digest                           =on    # on

performance-schema-instrument                                           ='memory/%=COUNTED'


#8.0 special
admin_address = 127.0.0.1  # null
admin_port = {{ (mysql_port*10 + 2) | string }}  # 33062
mysqlx_port = {{ (mysql_port*10) | string }} # 33060
event_scheduler=off   # on  5.7 is off，8.0 is on
sql_require_primary_key=on   # off
activate_all_roles_on_login=on  # off 开启角色
plugin-load-add=mysql_clone.so
default_authentication_plugin=mysql_native_password  # 8.0 is caching_sha2_password
explicit_defaults_for_timestamp = on # 8.0 on , 5.7 off
log_error_verbosity=3 # 8.0 is 2, 5.7 is 3
max_allowed_packet=64M  # 8.0 is 64M, 5.7 is 4M
{% if server_specs == "auto" %}
innodb_dedicated_server=on  # off
{% else %}
innodb_dedicated_server=off  # off
{% endif %}
loose-sql_generate_invisible_primary_key=on # off
terminology_use_previous=BEFORE_8_0_26 # none
binlog_expire_logs_seconds=604800 # In the event that expire_logs_days is not configured, the default value shall be 2592000 (equivalent to 30 days); otherwise, it will not take effect.																																														 
performance_schema_show_processlist=on # off

#percona special
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;validate_password=validate_password.so;audit_log.so"
loose-audit_log_file='{{ mysql_data_dir_base }}/log/binlog/{{mysql_port}}/audit.log'
loose-audit_log_rotations=99
loose-audit_log_rotate_on_size=1G
loose-audit_log_include_databases='test'
loose-audit_log_policy='QUERIES'
loose-audit_log_include_commands='create_table,delete,drop_table,alter_table,create_db,drop_db,set_option'

{% if db_type == 'greatsql' and fcs_use_greatsql_ha == 1 and make_mgr_role_included is defined and make_mgr_role_included %}
#GreatSQL MGR vip
plugin-load-add=greatdb_ha.so
loose-greatdb_ha_enable_mgr_vip=1
loose-greatdb_ha_mgr_vip_ip={{ greatsql_vip }}
loose-greatdb_ha_mgr_vip_mask={{ greatsql_netmask }}
loose-greatdb_ha_mgr_vip_nic={{ greatsql_net_work_interface }}

#single-primary mode
loose-group_replication_single_primary_mode=1
loose-group_replication_enforce_update_everywhere_checks=0
{% endif %}

